import numpy as np
import os
import pandas as pd
import pymysql

def get_conn(host='127.0.0.1', port=3306, user='root', password='123456', db_name='school_project', charset='utf8'):
    """
    获取mysql的连接资源
    :param host: 主机地址 = 'yhni.cn' @type {String}
    :param port: 端口号 = 3306 @type {Int}
    :param user: 用户名 = 'python' @type {String}
    :param password: 密码 = '123456' @type {String}
    :param db_name: 所用数据名字 = 'PYTHON2020' @type {String}
    :param charset:设定字符集  = 'utf8' @type {String}
    :return: mysql connection
    """
    return pymysql.connect(host=host, port=port,
                           user=user, password=password,
                           db=db_name, charset=charset)


def query_data(conn, sql):
    """
    根据SQL语句查询数据并返回结果
    读操作
    :param conn: 数据库连接资源
    :param sql: SQL语句
    :return:list[dict]
    """
    try:
        cursor = conn.cursor(pymysql.cursors.DictCursor)  ##使每行数据以字典的类型返回
        cursor.execute(sql)
        return cursor.fetchall()
    finally:
        conn.close()


def insertMany(conn, sql, data):
    """
    根据SQL语句执行数据操纵并返回结果
    写操作
    :param conn: 数据库连接资源
    :param sql: SQL语句
    :return:不返回内容
    """
    try:
        cursor = conn.cursor()
        cursor.executemany(sql, data)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        conn.close()
if __name__ == '__main__':
    conn = get_conn()
    data = pd.read_csv("static\heart.csv")
    print(data)
    datalist = data.values.tolist()
    # sql = "insert into 31801164_books value (null,'python_进阶','andy','zucc','{0}','{1}',3333)".format(date_day, s);
    sql = "insert into a_zucc_school_project_users values(null,'t123456',0,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    insertMany(conn, sql, datalist)
